"""
飞检数据STEP8筛选 - summary_dip模块
============================================================================

将 VLT2 表中的信息按照每个 hsp_abbr、每个 rule_id 做汇总统计。

主要功能：
1. 对 DIP 中的数据进行汇总统计。
2. 输出到本目录下 summary/vlt2_dip_summary.xlsx

备注：VLT2 的生成过程参考 step8_2_conclude.py。
============================================================================
"""

import os
import sys
import time
import pandas as pd
from sqlalchemy import text

sys.path.append(os.path.abspath(os.path.join(os.path.dirname(__file__), '..')))
from config import create_db_engine
from sqlalchemy.engine import Engine


# 时间函数
t0 = time.time()
def elapsed() -> str:
    timeStr = time.strftime("%H:%M:%S", time.localtime())
    delta = int(time.time() - t0)
    if delta < 60:
        return f"{timeStr} (+ {delta} sec)"
    elif delta < 3600:
        m, s = divmod(delta, 60)
        return f"{timeStr} (+ {m} min {s} sec)"
    elif delta < 86400:
        h, rem = divmod(delta, 3600)
        m, s = divmod(rem, 60)
        return f"{timeStr} (+ {h} hour {m} min {s} sec)"
    else:
        d, rem = divmod(delta, 86400)
        h, rem = divmod(rem, 3600)
        m, s = divmod(rem, 60)
        return f"{timeStr} (+ {d} day {h} hour {m} min {s} sec)"


# ------------------------------
#  分析
# ------------------------------
def analyze_dip_case(
        engine: Engine, dip_case_table: str, hsp_abbr: str,
        price310: float, price390: float
) -> None:
    """导出 dip_case 表进行分析

    Args:
        engine (Engine): 数据库引擎
        dip_case_table (str): dip_case 表名
        output_path (str): 输出文件路径
    """
    query_sql = f"""
        SELECT
            dc.hsp_abbr, dc.dip_code, m.insutype,
            MAX(dc.dip_name) AS dip_name,
            /*
            MAX(dc.dx_pattern) AS dx_pattern,
            MAX(dc.dx_pattern_name) AS dx_pattern_name,
            MAX(dc.tx_pattern) AS tx_pattern,
            MAX(dc.tx_pattern_name) AS tx_pattern_name,
            MAX(dc.dip_group_type) AS dip_group_type,
            MAX(dc.dip_dx_type) AS dip_dx_type,
            MAX(dc.dip_tx_type) AS dip_tx_type,
            MAX(dc.dip_rank_type) AS dip_rank_type,
            */
            MAX(dc.dip_score) AS dip_score,
            CASE 
                WHEN m.insutype = '310' THEN {price310}
                WHEN m.insutype = '390' THEN {price390}
            ELSE 0 END AS price,
            MAX(dc.dip_score) * CASE 
                WHEN m.insutype = '310' THEN {price310}
                WHEN m.insutype = '390' THEN {price390}
            ELSE 0 END AS dip_amount,
            dc.top_oper_code,
            MAX(dc.top_oper_name) AS top_oper_name,
            dc.p,
            COUNT(*) AS case_count,
            AVG(m.gnr_c) as avg_c,
            STDDEV(m.gnr_c) as stddev_c,
            MIN(m.gnr_c) as min_c,
            MAX(m.gnr_c) as max_c,
            PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY m.gnr_c) AS p25_c,
            PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY m.gnr_c) AS p50_c,
            PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY m.gnr_c) AS p75_c
        FROM {dip_case_table} dc, m
        WHERE dc.setl_id = m.setl_id AND dc.hsp_abbr = '{hsp_abbr}'
        GROUP BY dc.hsp_abbr, dc.dip_code, dc.top_oper_code, m.insutype, p
        ORDER BY dc.hsp_abbr, dc.dip_code, dc.top_oper_name, m.insutype, p DESC
    """
    with engine.connect() as conn:
        df = pd.read_sql(query_sql, conn)
        print(f"[{elapsed()}]Exported analysis.")
    return df

def analyze_dip(
        engine: Engine, dip_case_table: str, hsp_abbr: str,
        price310: float, price390: float
) -> None:
    """导出 dip 表进行分析

    Args:
        engine (Engine): 数据库引擎
        dip_table (str): dip 表名
        output_path (str): 输出文件路径
    """
    query_sql = f"""
        SELECT
            dip.hsp_abbr, dip.dip_code, m.insutype,
            MAX(dip.dip_name) AS dip_name,
            MAX(dip.dx_pattern) AS dx_pattern,
            MAX(dip.dx_pattern_name) AS dx_pattern_name,
            MAX(dip.tx_pattern) AS tx_pattern,
            MAX(dip.tx_pattern_name) AS tx_pattern_name,
            MAX(dip.dip_group_type) AS dip_group_type,
            MAX(dip.dip_dx_type) AS dip_dx_type,
            MAX(dip.dip_tx_type) AS dip_tx_type,
            MAX(dip.dip_rank_type) AS dip_rank_type,
            MAX(dip.dip_score) AS dip_score,
            CASE 
                WHEN m.insutype = '310' THEN {price310}
                WHEN m.insutype = '390' THEN {price390}
            ELSE 0 END AS price,
            MAX(dip.dip_score) * CASE 
                WHEN m.insutype = '310' THEN {price310}
                WHEN m.insutype = '390' THEN {price390}
            ELSE 0 END AS dip_amount,
            COUNT(DISTINCT dip.setl_id) AS setl_count,
            AVG(m.gnr_c) as avg_c,
            STDDEV(m.gnr_c) as stddev_c,
            AVG(m.gnr_c) / MAX(CASE 
                WHEN m.insutype = '310' THEN dip.dip_score * {price310}
                WHEN m.insutype = '390' THEN dip.dip_score * {price390}
            ELSE 1 END) AS dip_ratio,
            COUNT(DISTINCT CASE WHEN m.gnr_c >= 2 * dip.dip_score * CASE 
                WHEN m.insutype = '310' THEN {price310}
                WHEN m.insutype = '390' THEN {price390}
            ELSE 0 END THEN dip.setl_id END) AS upper_count,
            COUNT(DISTINCT CASE WHEN m.gnr_c < 0.5 * dip.dip_score * CASE 
                WHEN m.insutype = '310' THEN {price310}
                WHEN m.insutype = '390' THEN {price390}
            ELSE 0 END THEN dip.setl_id END) AS lower_count
        FROM {dip_case_table} dip, m
        WHERE dip.setl_id = m.setl_id AND dip.hsp_abbr = '{hsp_abbr}'
        GROUP BY dip.hsp_abbr, dip.dip_code, m.insutype
        ORDER BY dip.hsp_abbr, dip_ratio DESC
    """
    with engine.connect() as conn:
        df = pd.read_sql(query_sql, conn)
        print(f"[{elapsed()}]Exported analysis")
    return df

def main():
    print(f"[{elapsed()}]Starting step8_5_summary_dip...")

    # 创建数据库引擎
    engine = create_db_engine()

    # 分析
    dip_table_name = "dip"
    dip_case_table_name = "dip_case"
    price310 = 14.66
    price390 = 10.76
    hspFactor = 0.9

    # 获得所有医院简称
    with engine.connect() as conn:
        hsp_abbrs = pd.read_sql(text(f"SELECT DISTINCT hsp_abbr FROM {dip_table_name}"), conn)["hsp_abbr"].tolist()

    for hsp_abbr in hsp_abbrs:
        print(f"[{elapsed()}] Processing hospital: {hsp_abbr} ...")

        # 特殊病例类型分析
        dfWork = analyze_dip_case(
            engine, 
            dip_case_table_name, 
            hsp_abbr,
            price310 * hspFactor, 
            price390 * hspFactor
        )

        # 总体分析
        dfTotal = analyze_dip(
            engine, 
            dip_case_table_name, 
            hsp_abbr,
            price310 * hspFactor, 
            price390 * hspFactor
        )

        # 放到同一个excel当中
        with pd.ExcelWriter(os.path.join(os.path.dirname(__file__), 'summary', f'dip_summary_{hsp_abbr}.xlsx')) as writer:
            dfWork.to_excel(writer, sheet_name='dip_case_analysis', index=False)
            dfTotal.to_excel(writer, sheet_name='dip_analysis', index=False)

        print(f"[{elapsed()}] {hsp_abbr} analysis completed.")
if __name__ == "__main__":
    main()